package com.itheima.dao;

import com.itheima.Vo.BusinessReportVo;
import com.itheima.entity.MemberReportSelectVo;
import com.itheima.entity.SetmealCount;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface ReportDao {

    /**
     * 会员数量统计
     * @return
     */
    @Select("SELECT LAST_DAY(DATE_FORMAT(regTime, '%Y-%m-01')) AS months,count(*) as memberCount FROM t_member group by months order by months")
    List<MemberReportSelectVo> getMemberReport();


    /**
     * 获取预约表的套餐名数据
     * @return
     */
    @Select("select ts.name from t_order to2 ,t_setmeal ts where to2.setmeal_id = ts.id group by ts.name")
    List<String> getSetmealNames();

    /**
     * 根据套餐名/id查询对应数量
     * @return
     */
    @Select("select ts.name name,count(ts.name) value from t_order to2 ,t_setmeal ts where to2.setmeal_id = ts.id group by ts.name")
    List<SetmealCount> getSetmealCount();


    /**
     *获取本日、本周、本月新增会员数
     * @param reportDate
     * @return
     */
    @Select("SELECT\n" +
            "    (SELECT COUNT(*) FROM t_member WHERE DATE(regTime) = #{reportDate}) AS todayNewMember,\n" +
            "    (SELECT COUNT(*) FROM t_member WHERE YEARWEEK(regTime, 1) = YEARWEEK(#{reportDate}, 1)) AS thisWeekNewMember,\n" +
            "    (SELECT COUNT(*) FROM t_member WHERE YEAR(regTime) = YEAR(#{reportDate}) AND MONTH(regTime) = MONTH(#{reportDate})) AS thisMonthNewMember,\n" +
            "    (select count(*)  from t_member)as totalMember;")
    BusinessReportVo getMemberCountByDate(LocalDate reportDate);

    /**
     * //获取本日本周本月预约数到诊数
     * @param reportDate
     * @return
     */
    @Select("SELECT\n" +
            "    (SELECT COUNT(*) FROM t_order WHERE DATE(orderDate) = #{reportDate}) AS todayOrderNumber,\n" +
            "    (SELECT COUNT(*) FROM t_order WHERE YEARWEEK(orderDate, 1) = YEARWEEK(#{reportDate}, 1)) AS thisWeekOrderNumber,\n" +
            "    (SELECT COUNT(*) FROM t_order WHERE YEAR(orderDate) = YEAR(#{reportDate}) AND MONTH(orderDate) = MONTH(#{reportDate})) AS thisMonthOrderNumber,\n" +
            "    (SELECT COUNT(*) FROM t_order WHERE DATE(orderDate) = #{reportDate} AND orderStatus='已到诊') AS todayVisitsNumber,\n" +
            "    (SELECT COUNT(*) FROM t_order WHERE YEARWEEK(orderDate, 1) = YEARWEEK(#{reportDate}, 1) AND orderStatus='已到诊') AS thisWeekVisitsNumber,\n" +
            "    (SELECT COUNT(*) FROM t_order WHERE YEAR(orderDate) = YEAR(#{reportDate}) AND MONTH(orderDate) = MONTH(#{reportDate}) AND orderStatus='已到诊' ) AS thisMonthVisitsNumber")
    BusinessReportVo getOrderCountByDate(LocalDate reportDate);
}
